package com.campus.server.dao;


import com.campus.common.util.DBUtil;
import com.campus.common.vo.Book;
import com.campus.common.vo.Student;
import com.campus.common.vo.StudentRequest;

import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

public class StudentDao {
    public boolean isRegister(String username, String password,Student student) {
        try (Connection conn = DBUtil.getConnection(); //从DBUtil获取数据库连接
             Statement st = conn.createStatement(); //创建Statement对象用于执行SQL
             ResultSet rs = st.executeQuery("SELECT * FROM user1")) { //执行查询，返回结果集
            //遍历结果集的每一行
            while (rs.next()) {
                if (rs.getString("username").equals(username)) {
                    return false;
                }
            }
        } catch (Exception e) { e.printStackTrace(); }
        int rows_1=0;
        int rows_2=0;
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(
                     "INSERT INTO user1 (username, password, role) VALUES (?, ?, ?)")) {
            conn.setAutoCommit(true);  // 确保立即提交
            ps.setString(1, username);
            ps.setString(2, password);
            ps.setString(3, "学生");
            rows_1 = ps.executeUpdate();
            System.out.println("更新行数: " + rows_1); // 调试用
        } catch (Exception e) { e.printStackTrace(); }

        String studentId = student.getStudentId();
        String name=student.getStudentName();
        String sex=student.getAcademicRecord().getSex();
        int age=student.getAcademicRecord().getAge();
        String className=student.getAcademicRecord().getClassName();
        String grade=student.getAcademicRecord().getGrade();
        String enrollDate=student.getAcademicRecord().getEnrollDate();
        String contactInfo=student.getAcademicRecord().getContactInfo();
        String major=student.getAcademicRecord().getMajor();
        String recordId=student.getAcademicRecord().getRecordId();
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(
                     "INSERT INTO student1 (studentId, name, sex, age, className, grade, major, enrollDate, contactInfo, recordId, status) " +
                             "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")) {
            conn.setAutoCommit(true);  // 确保立即提交
            ps.setString(1, studentId);
            ps.setString(2, name);
            ps.setString(3, sex);
            ps.setInt(4, age);
            ps.setString(5, className);
            ps.setString(6, grade);
            ps.setString(7, major);
            ps.setString(8, enrollDate);
            ps.setString(9, contactInfo);
            ps.setString(10, recordId);
            ps.setString(11, "在读");
            rows_2 = ps.executeUpdate();
            System.out.println("更新行数: " + rows_2); // 调试用
            return rows_1*rows_2 > 0;
        } catch (Exception e) { e.printStackTrace(); }
        return false;
    }
    public String getStudentNameById(String studentId) {
        String name = "";
        String sql = "SELECT name FROM student1 WHERE studentId = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, studentId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                name = rs.getString("name");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return name;
    }
    public boolean addStudentRequest(String studentId,String contactInfo,String oldContactInfo){
        try (Connection conn = DBUtil.getConnection(); //从DBUtil获取数据库连接
             Statement st = conn.createStatement(); //创建Statement对象用于执行SQL
             ResultSet rs = st.executeQuery("SELECT * FROM studentRequest")) { //执行查询，返回结果集
            //遍历结果集的每一行
            while (rs.next()) {
                if (rs.getString("studentId").equals(studentId)) {
                    return false;
                }
            }
        } catch (Exception e) { e.printStackTrace(); }
        int rows_1=0;
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(
                     "INSERT INTO studentRequest (studentId,contactInfo,oldContactInfo,time) VALUES (?, ?, ?,?)")) {
            conn.setAutoCommit(true);  // 确保立即提交
            ps.setString(1, studentId);
            ps.setString(2, contactInfo);
            ps.setString(3, oldContactInfo);
            ps.setTimestamp(4, Timestamp.valueOf(LocalDateTime.now()));
            rows_1 = ps.executeUpdate();
            System.out.println("更新行数: " + rows_1);
            return rows_1>0;
        } catch (Exception e) { e.printStackTrace(); }
        return false;
    }

    public boolean deleteStudentRequest(String studentId,String contactInfo) {
        String sql_1="DELETE FROM studentRequest WHERE studentId=? AND contactInfo=?";

        int rowsAffected_1=0;

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt_1 = conn.prepareStatement(sql_1)) {

            stmt_1.setString(1, studentId);
            stmt_1.setString(2, contactInfo);
            rowsAffected_1 = stmt_1.executeUpdate();
            System.out.println("studentId=" + studentId);
            System.out.println("rowsAffected = " + rowsAffected_1);
            System.out.println(rowsAffected_1>0);

            return rowsAffected_1>0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }

    }

    public List<StudentRequest> listStudentRequests() {
        List<StudentRequest> requests = new ArrayList<>();
        String sql = "SELECT * FROM studentRequest";

        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                requests.add(new StudentRequest(
                        rs.getString("studentId"),
                        rs.getString("contactInfo"),
                        rs.getString("oldContactInfo"),
                        rs.getString("time")

                ));
                System.out.print("1");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return requests;
    }

    public boolean updateStudent(String studentId,String contactInfo){
        String sql = "UPDATE student1 SET contactInfo=? WHERE studentId=?";

        int rowsAffected_1=0;

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt_1 = conn.prepareStatement(sql)) {

            stmt_1.setString(2, studentId);
            stmt_1.setString(1, contactInfo);
            rowsAffected_1 = stmt_1.executeUpdate();
            System.out.println("studentId=" + studentId);
            System.out.println("rowsAffected = " + rowsAffected_1);
            System.out.println(rowsAffected_1>0);

            return rowsAffected_1>0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }


}
